﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;

namespace KBLPrint
{
    public class SqlLiteManager
    {
        //SQLiteDataAdapter sa = null;
        SQLiteConnection con = null;
        string sqliteConnnectionString = string.Empty;

        public bool OpenDB(string sPath , string sPassword , out string sErrInfo)
        {
            try
            {
                sErrInfo = string.Empty;
                sqliteConnnectionString = string.Format("Provider=System.Data.SQLite;Data Source={0};Persist Security Info=False", sPath);
                con = new SQLiteConnection(sqliteConnnectionString);
                if (!string.IsNullOrEmpty(sPassword))
                {
                    con.SetPassword(sPassword);
                }
                con.Open();

                //string sNewPassword = "KBL2412";
                ////修改密码
                //con.ChangePassword(sNewPassword);

                return true;
            }
            catch (Exception ex)
            {
                sErrInfo = ex.Message;
                return false;
            }
           
        }

        public  int ExecuteSql(string sql)
        {
            try
            {
                int nCnt = 0;
                if (con == null) return 0;
                if (con.State != System.Data.ConnectionState.Open)
                    con.Open();

                SQLiteCommand cmd = new SQLiteCommand(con);
                cmd.CommandText = sql;
                nCnt = cmd.ExecuteNonQuery();
                cmd.Dispose();
                return nCnt;
            }
            catch (Exception ex)
            {
                Console.WriteLine(string.Format("ExecuteSql:{0}", ex.Message.ToString()));
                return 0;
            }
        }

        //string sErrMsg = string.Empty;
        //public DataTable GetDataSet(string sql)
        //{
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        SQLiteDataAdapter slda = new SQLiteDataAdapter(sql, con);
        //        slda.Fill(ds);
        //        if (ds.Tables.Count <= 0)
        //        {
        //            return new DataTable();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        sErrMsg = ex.Message;
        //    }
        //    return ds.Tables[0];
        //}

        /// <summary>
        /// 执行带sql语句，结果返回在DataTable数组里
        /// </summary>
        /// <param name="conn">连接字符串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="dt">返回DataTable</param>
        /// <param name="sMsg">返回错误信息</param>
        /// <returns>true执行成功，false执行失败</returns>
        public bool GetDataSet( string sql, out DataTable dt, out string sMsg)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(con);
                cmd.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                sMsg = "";
                dt = ds.Tables[0];//返回数组
                cmd.Dispose();

                return true;
            }
            catch (SQLiteException ex)
            {
                sMsg = ex.Message;
                dt = new DataTable();
                return false;
            }
        }


        public bool ExecuteSql(List<string> SQLList)
        {
            using (SQLiteTransaction tran = con.BeginTransaction())
            {
                SQLiteCommand command = new SQLiteCommand();
                command.Connection = con;
                string ErrorSQL = "";
                try
                {
                    foreach (string SQL in SQLList)
                    {
                        ErrorSQL = SQL;
                        command.CommandText = SQL;
                        command.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (Exception )
                {
                    tran.Rollback();
                    string sql = string.Empty;
                    foreach (var temp in SQLList)
                    {
                        sql += temp;
                    }
                    return false;
                }
                command.Dispose();
            }
      
            return true;
        }

    }
}
